import os
import sys
import sqlite3
from urllib.parse import urljoin
from lxml import etree
import pandas as pd
import numpy as np
import json
import requests
from tqdm import tqdm
from geo import Baidu_Map

# -*- coding: utf-8 -*-
# @Time        : 2023/11/18
# @Author      : keyan.kalami
# @Email       : yclu@aioaiofm.ac.cn
# @File        : noaa.py
# @Project     : noaa
# @Description : 获取并处理NOAA数据集的主程序

def __handle_file_error():
    try:  
        conn = sqlite3.connect('example.db')  
        print("sqlite3 is already installed")  
    except sqlite3.Error as e:  
        print("sqlite3 is not installed：", e)  
    finally:  
        conn.close() 

    if 'lxml' in sys.modules:  
        print("lxml is already installed")  
    else:  
        print("lxml is not installed")  

    if 'requests' in sys.modules:  
        print("Requests is already installed")  
    else:  
        print("Requests is not installed")  

with open('/root/Programs/NovaPrograms/set/_ads.json', 'r', encoding='utf-8') as file:  
    VALUE_SETS = json.load(file)

class NOAA:
    #download and process noaa datas
    def __init__(self):

        self.folder = VALUE_SETS["NOAA_FOLDER"]
        self.noaa_archive_url = VALUE_SETS["NOAA_ARC_URL"]
        self.noaa_station_api = VALUE_SETS["NOAA_STA_URL"]

        #create session
        self.s = requests.Session()

        #configuration database
        self.conn = sqlite3.connect(VALUE_SETS["DB_NAME"])
        self.cursor = self.conn.cursor()

        #create meteorology table
        #table1:info
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS info 
                   (
                       station_id CHAR(12) NOT NULL,   
                       name       VARCHAR(50),
                       latitude   FLOAT,
                       longitude  FLOAT,
                       country    VARCHAR(20),
                       province   VARCHAR(20),
                       city       VARCHAR(20),
                       district   VARCHAR(20),
                       PRIMARY KEY (station_id)
                   );""")
        #table2:data

        self.cursor.execute("""CREATE TABLE IF NOT EXISTS data
                   (
                       station    CHAR(12) NOT NULL,
                       date       DATE NOT NULL,
                       temp       FLOAT,
                       dewp       FLOAT,
                       slp        FLOAT,
                       stp        FLOAT,
                       visib      FLOAT,
                       wdsp       FLOAT,
                       mxspd      FLOAT,
                       gust       FLOAT,
                       max        FLOAT,
                       min        FLOAT,
                       prcp       FLOAT,
                       sndp       FLOAT,
                       frshtt     int,
                       PRIMARY KEY (station, date)
                   );""")
        
        self.baidu_api = None

    def _download_tar_gz(self,url) -> None:
        """
        download noaa file from url.

        Parameters
        ----------
        url : str
            The url of file

        Such as:
        -------
        _download_tar_gz('https://nomads.ncep.noaa.gov/.../gdas1.nov23.w2')

        Returns:
        -------
            None
        """
        if not os.path.exists(self.folder) or os.path.isfile(self.folder):
            os.mkdir(self.folder)
        
        #设置文件存储名称
        file_name = url.split('/')[-1]  #提取url最后一个元素
        target_file_path = os.path.join(self.folder,file_name)
        resp = self.s.get(url,stream=True)
        if resp.status_code == 200:
                with open(target_file_path, 'wb') as f,tqdm(unit='B', unit_scale=True, unit_divisor=1024) as bar:
                    for chunk in resp.raw.stream(1024, decode_content=False):
                        if chunk:
                            f.write(chunk)
                            bar.update(len(chunk))
                    print(f'successfully downloaded compressed file {file_name} !')
        else:
            print(f'trying to access{url}error!')

    #数据归并
    def _extrat_file_to_sql(self) -> None:
        """
        extrat tar.gz file and save datas to database.

        you can modify the path information in the -ads.json file.
        """
        #解压所有文件
        """
        @description:暂时解压操作屏蔽
        """
        # for item in os.listdir(self.folder):
        #     tar_file_path = os.path.join(self.folder,item)
        #     if os.path.isfile(tar_file_path) and item.split('.')[-1] == 'gz':
        #         self._extract_tar(tar_file_path)
        test_no = 0
        #存放至数据库
        for root, _, file in os.walk(VALUE_SETS["CSV_ADDRESS"]):
            for s_file in file:
                if s_file.split('.')[-1] == 'csv':
                    if test_no == 0:
                        current_path = os.path.join(root,s_file)
                        self._insert_single_csv(current_path,"data")
                        self._download_geo_mes_to_sql(current_path)
                        test_no += 2
                    else:break
                    
    #解压文件
    def _extract_tar(self,file_path:str) -> None:
        """
        just extract only a single file.

        Parameters
        ----------
        file_path : str
            The path of file

        Such as:
        -------
        _extract_tar('/root/Programs/tar_gz')

        Returns:
        -------
            None
        """        
        import tarfile
        tar = tarfile.open(file_path)
        folder_path,file_path = os.path.split(file_path)
        target_folder = os.path.join(folder_path,file_path.split('.')[0])
        #如果文件存在，则解压已完成，return
        # if os.path.exists(target_folder) and os.path.isfile(target_folder):
        #     return
        # else:
        #     os.mkdir(target_folder)
        #解压文件
        #tar.extract(target_folder)
        tar.extract("/root/Programs/tar_gz/2023")
        print(f'file {file_path} extract success ！')

    #将单个csv数据插入数据库中
    def _insert_single_csv(self,csv_path:str,table:str) -> None:
        """
        insert a single csv data into the database.

        Parameters
        ----------
        file_path : str
            The path of file csv

        Such as:
        -------
        _extract_tar('/root/Programs/tar_gz/2023/01001099999.csv')

        Returns:
        -------
            None
        """ 
        if table == "data":
            print(f'begin insert datas in {csv_path} to database......')
            try:
                df = pd.read_csv(csv_path, 
                usecols=['STATION', 'DATE', 'TEMP', 'DEWP', 'SLP', 'STP', 'VISIB', 'WDSP',
                'MXSPD', 'GUST', 'MAX', 'MIN', 'PRCP', 'SNDP', 'FRSHTT'])
            except:
                print(f'process {csv_path} error')
                return 
            
            #拼接sql语句，准备插入数据库
            sql_statement = "INSERT OR IGNORE INTO data(STATION, DATE, TEMP, DEWP, SLP, STP, VISIB, WDSP, " \
                            "MXSPD, GUST, MAX, MIN, PRCP, SNDP, FRSHTT) VALUES"

            value = ''
            for item in df.loc[:, ['STATION', 'DATE', 'TEMP', 'DEWP', 'SLP',
                                'STP', 'VISIB', 'WDSP', 'MXSPD', 'GUST',
                                'MAX', 'MIN', 'PRCP', 'SNDP', 'FRSHTT']].itertuples(index=False):
                item = [f'"{item[index]}"' for index in range(len(item))]
                value += f"({', '.join(item)}),"
            sql_statement += value[:-1] + ';'

            #执行插入操作，向数据库中插入数据
            self.cursor.execute(sql_statement)
            self.conn.commit()

        #if table == "info":

    #下载站点地理信息存入数据库
    def _download_geo_mes_to_sql(self,csv_path:str) -> None:
        """
        download geographic information from Baidu map according to latitude and longitude 
        and store it in the database

        Parameters
        ----------
        csv_path : str
            The path of file csv

        Returns:
        -------
            None
        """         
        print(f'begin download geo_infos in {csv_path} to database......')
        try:
            df = pd.read_csv(csv_path, 
            usecols=['STATION', 'LATITUDE','LONGITUDE'])
        except:
            print(f'process {csv_path} error')
            return   

        geo_obj = Baidu_Map()
        
        sql_statement = "INSERT OR IGNORE INTO info(station_id,latitude,longitude) VALUES"
        value = ''
        for item in df.loc[:,['STATION', 'LATITUDE','LONGITUDE']].itertuples(index=False):
            item = [f'"{item[index]}"' for index in range(len(item))]
            value += f"({', '.join(item)}),"

        sql_statement += value[:-1] + ';'

        #执行插入操作，向数据库中插入数据
        self.cursor.execute(sql_statement)
        self.conn.commit()

        #for item in range(len(df)):
        _loc:dict = geo_obj.get_location(df.loc[0,'LATITUDE'],df.loc[0,'LONGITUDE'])
        if _loc != None:
            try:  
                self.cursor.execute("INSERT OR IGNORE INTO info(country,province,city,district) VALUES (?, ?, ?, ?)",   
                                (_loc['country'],_loc['province'],_loc['city'],_loc['district']))  
                self.conn.commit()  
            except Exception as e:  
                print(f"插入数据时出错：{e}")  
                self.conn.rollback()  # 如果插入失败，回滚数据库连接  
  
    #插入单个站点的地理信息
    def _insert_single_station(self,station_id:str) -> None:
        """
        param: station_id
        return: none
        """
        print(f'正在获取{station_id}经纬度信息...')
        params = {
            'stations': station_id,
            'dataset': 'global-summary-of-the-day',
            'limit': '1',
        }     
        try:
            resp = self.s.get(url=self.noaa_station_api,params=params).json()
            info = {
                'station_id': station_id,
                'name': resp['results'][0]['stations'][0]['name'],
                'latitude': resp['bounds']['bottomRight']['lat'],
                'longitude': resp['bounds']['bottomRight']['lon'],
            }
        except:
            print(f'获取站点 {station_id} 相关信息时出错！')
            return 
        location = self.baidu_api.get_location(info['latitude'], info['longitude'])
        #info.update(location)

# def main():
#     _noaa_obj = NOAA()
#     _noaa_obj._download_tar_gz("https://nomads.ncep.noaa.gov/pub/data/nccf/com/hysplit/prod/gdas.202311/gdas1.nov23.w2")
#     #_noaa_obj._extrat_file_to_sql()
#     #_noaa_obj._download_geo_mes_to_sql('/root/Programs/tar_gz/2023/01001099999.csv')

# if __name__ == "__main__":
#     main()
 